﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{
    public class ProductCategoryGateway : DbGateway
    {

        public string SaveProductCategory(ProductCategory aProductCategory)
        {
            try
            {
                string insert = String.Format("INSERT INTO PosProductCategory VALUES( @categoryName, @genderId)");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = insert;
                CommandObj.Parameters.Add("@categoryName", SqlDbType.VarChar);
                CommandObj.Parameters["@categoryName"].Value = aProductCategory.CategoryName;
                CommandObj.Parameters.Add("@genderId", SqlDbType.Int);
                CommandObj.Parameters["@genderId"].Value = aProductCategory.AProductGender.GenderId;
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Form Couldn't Saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "Save Sucessfully";
        }

        public List<ProductCategory> GetAllProductCategory()
        {
            List<ProductCategory> forms = new List<ProductCategory>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosProductCategory");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    ProductCategory aProductCategory = new ProductCategory();
                    aProductCategory.CategoryId = Convert.ToInt32(reader["CategoryId"]);
                    aProductCategory.CategoryName = reader["CategoryName"].ToString();
                    forms.Add(aProductCategory);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Form loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return forms;
        }
        public ProductCategory FindProductCategory(int categoryId)
        {
            try
            {
                string search = String.Format("Select * FROM PosProductCategory WHERE CategoryId=@categoryId");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@categoryId", SqlDbType.Int);
                CommandObj.Parameters["@categoryId"].Value = categoryId;
                CommandObj.CommandText = search;
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                ProductCategory form = new ProductCategory();
                if (sqlDataReader != null)
                {
                    while (sqlDataReader.Read())
                    {
                        form.CategoryId = Convert.ToInt32(sqlDataReader["CategoryId"]);
                        form.CategoryName = sqlDataReader["CategoryName"].ToString();
                        return form;
                    }
                }
                return null;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found ProductCategory", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public string DeleteCategoryById(int CategoryId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosProductCategory WHERE CategoryId='{0}'", CategoryId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "Category Id does not found";
            }
        }

        public List<ProductCategory> GetAllCategoryByIdOrName(int i, string categoryId)
        {
            List<ProductCategory> aProductCategories = new List<ProductCategory>();
            string select = "";

            try
            {
                ConnectionObj.Open();
                if (i == 0)
                {

                    select = String.Format("SELECT * FROM PosProductCategory WHERE categoryId ='{0}'", Convert.ToInt32(categoryId));
                }
                if (i == 1)
                {
                    categoryId = categoryId + "%";
                    select = String.Format("SELECT * FROM PosProductCategory WHERE categoryName LIKE '" + categoryId + "'");
                }

                CommandObj.CommandText = select;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    ProductCategory productCategory = new ProductCategory();
                    productCategory.CategoryId = Convert.ToInt32(reader[0]);
                    productCategory.CategoryName = reader[1].ToString();
                    aProductCategories.Add(productCategory);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return aProductCategories;
        }

    }
}